package com.lsyw.intergrate.common.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

import static org.apache.poi.ss.usermodel.BorderStyle.THIN;

/**
 * @author 刘磊
 * @version 1.0
 * @date 2020/8/31 13:54
 * easyExcel工具类
 */
public class EasyExcelUtil {
    /**
     * 功能描述: <br>
     * 〈报表导出处理〉
     *
     * @Param: [response, listdata 查询, filename 导出表格名字, clazz 数据对应的实体类]
     * @Return: void
     * @Author: 刘磊
     * @Date: 2020/8/31 13:55
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public static void toExcle(HttpServletResponse response, List listdata, String filename, Class clazz) {
        try {
            //设置响应格式
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + name + ".xlsx");
            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();


            //------------------------------------------excel调整样式开始---------------------------------------------------------/
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);

            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(THIN);
            contentWriteCellStyle.setBorderTop(THIN);
            contentWriteCellStyle.setBorderRight(THIN);
            contentWriteCellStyle.setBorderBottom(THIN);

            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            //------------------------------------------excel调整样式结束---------------------------------------------------------/

            //获取工作簿对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();

            //获取工作表数量
            Integer sheetnum = 1;
            if (listdata.size() < 1000000) {
                sheetnum = 1;
            } else {
                sheetnum = listdata.size() % 1000000 == 0 ? listdata.size() / 1000000 : listdata.size() / 1000000 + 1;//这里我是根据自身工作需要设置每一百万条数据一个sheet
            }
            // 去调用写入,这里我调用了sheetnum次，实际使用时根据数据库分页的总的页数来。这里最终会写到sheetnum个sheet里面
            List<T> subList = null;
            for (int i = 0; i < sheetnum; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo
                int num = i+1;
                WriteSheet writeSheet = EasyExcel.writerSheet(i, filename +"sheet"+num).registerWriteHandler(horizontalCellStyleStrategy).build();
                //这里是将查询的数据分为若干个sheet页
                subList = listdata.subList(i * 1000000, i == sheetnum - 1 ? listdata.size() : (i + 1) * 1000000);
                excelWriter.write(subList, writeSheet);
            }
            //finish 会帮忙关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
  public static void toExcle2(HttpServletResponse response, List listdata, String filename, Class clazz) {
        try {
            //设置响应格式
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + name + ".xlsx");
            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();


            //------------------------------------------excel调整样式开始---------------------------------------------------------/
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);

            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(THIN);
            contentWriteCellStyle.setBorderTop(THIN);
            contentWriteCellStyle.setBorderRight(THIN);
            contentWriteCellStyle.setBorderBottom(THIN);

            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            //------------------------------------------excel调整样式结束---------------------------------------------------------/

            //获取工作簿对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();

            //获取工作表数量
            Integer sheetnum = 1;
            if (listdata.size() < 1000000) {
                sheetnum = 1;
            } else {
                sheetnum = listdata.size() % 1000000 == 0 ? listdata.size() / 1000000 : listdata.size() / 1000000 + 1;//这里我是根据自身工作需要设置每一百万条数据一个sheet
            }
            // 去调用写入,这里我调用了sheetnum次，实际使用时根据数据库分页的总的页数来。这里最终会写到sheetnum个sheet里面
            List<T> subList = null;
            for (int i = 0; i < sheetnum; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo
                int num = i+1;
                WriteSheet writeSheet = EasyExcel.writerSheet(i, filename +"sheet"+num).registerWriteHandler(horizontalCellStyleStrategy).build();
                //这里是将查询的数据分为若干个sheet页
                subList = listdata.subList(i * 1000000, i == sheetnum - 1 ? listdata.size() : (i + 1) * 1000000);
                excelWriter.write(subList, writeSheet);
            }
            //finish 会帮忙关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void toExcle(HttpServletResponse response, List listdata, WriteTable table, String filename) {
        try {
            //设置响应格式
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = URLEncoder.encode(filename, "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + name + ".xlsx");

            //获取输出流
            ServletOutputStream outputStream = response.getOutputStream();

            //获取工作簿对象
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            //------------------------------------------excel调整样式开始---------------------------------------------------------/
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);

            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //设置边框样式
            contentWriteCellStyle.setBorderLeft(THIN);
            contentWriteCellStyle.setBorderTop(THIN);
            contentWriteCellStyle.setBorderRight(THIN);
            contentWriteCellStyle.setBorderBottom(THIN);

            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            //------------------------------------------excel调整样式结束---------------------------------------------------------/

            //获取工作表数量
            Integer sheetnum = 1;
            if (listdata.size() < 1000000) {
                sheetnum = 1;
            } else {
                sheetnum = listdata.size() % 1000000 == 0 ? listdata.size() / 1000000 : listdata.size() / 1000000 + 1;//这里我是根据自身工作需要设置每一百万条数据一个sheet
            }
            // 去调用写入,这里我调用了sheetnum次，实际使用时根据数据库分页的总的页数来。这里最终会写到sheetnum个sheet里面
            List<T> subList = null;
            for (int i = 0; i < sheetnum; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo
                WriteSheet writeSheet = EasyExcel.writerSheet(i, filename + i).registerWriteHandler(horizontalCellStyleStrategy).build();
                //这里是将查询的数据分为若干个sheet页
                subList = listdata.subList(i * 1000000, i == sheetnum - 1 ? listdata.size() : (i + 1) * 1000000);
                excelWriter.write(subList, writeSheet, table);
            }
            //finish 会帮忙关闭流
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
